﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;

namespace Lab6_Product
{
    class DataProccess
    {
        SqlConnection conn = null;
        public DataProccess()
        {
            conn = new SqlConnection("server=ADMIN1105xbv\\SQLEXPRESS; database=Store; uid=sa; pwd=die4top");
        }
        public DataTable GetAllProduct()
        {
            string sql = "SELECT * FROM Products";
            DataTable dt = new DataTable();            
            SqlDataAdapter da = new SqlDataAdapter(sql, conn);
            da.Fill(dt);
            return dt;
        }

        public bool AddNewProduct(int id, string ProductName, string description, int priceIn, int priceSell, int quantity, string image)
        {
            if (conn.State == System.Data.ConnectionState.Closed)
                conn.Open();

            string sqlcom = "INSERT INTO Products VALUES(@id, @ProductName,@description, @image, @priceIn, @priceSell, @quantity)";
            SqlCommand cm = new SqlCommand(sqlcom, conn);
            cm.Parameters.AddWithValue("@ProductName", ProductName);
            cm.Parameters.AddWithValue("@description", description);
            cm.Parameters.AddWithValue("@priceIn", priceIn);
            cm.Parameters.AddWithValue("@priceSell", priceSell);
            cm.Parameters.AddWithValue("@quantity", quantity);
            cm.Parameters.AddWithValue("@image", image);
            cm.Parameters.AddWithValue("@id", id);
            int n = cm.ExecuteNonQuery();
            bool result = (n > 0);
            conn.Close();
            return result;
        }


        public Product GetData(int _id)
        {
            if (conn.State == System.Data.ConnectionState.Closed) conn.Open();
            string sqlcom = "SELECT * FROM Products where _id=@_id";
            SqlCommand cm = new SqlCommand(sqlcom, conn);
            cm.Parameters.AddWithValue("@_id", _id);
            SqlDataReader rd = cm.ExecuteReader();
            Product p = null;
            while (rd.Read())
            {
                p = new Product();
                p.Id = rd.GetInt32(0);
                p.ProductName = rd.GetString(1);
                p.Description = rd.GetString(2);
                p.PriceIn = rd.GetInt32(4);
                p.PriceSell = rd.GetInt32(5);
                p.Quantity = rd.GetInt32(6);
                p.Image = rd.GetString(3);
            }
            rd.Close();
            conn.Close();
            return p;
        }

        public bool EditProduct(int _id, string ProductName, string description, int priceIn, int priceSell, int quantity, string image)
        {
            
            
            if (conn.State == System.Data.ConnectionState.Closed)
                conn.Open();

            string sqlcom = "UPDATE Products SET ProductName= @ProductName, description=@description,image= @image, priceIn= @priceIn, priceSell= @priceSell,quantity= @quantity WHERE _id= @_id";
            SqlCommand cm = new SqlCommand(sqlcom, conn);
            cm.Parameters.AddWithValue("@ProductName", ProductName);
            cm.Parameters.AddWithValue("@description", description);
            cm.Parameters.AddWithValue("@priceIn", priceIn);
            cm.Parameters.AddWithValue("@priceSell", priceSell);
            cm.Parameters.AddWithValue("@quantity", quantity);
            cm.Parameters.AddWithValue("@image", image);
            cm.Parameters.AddWithValue("@_id", _id);
            int n = cm.ExecuteNonQuery();
            bool result = (n > 0);
            conn.Close();
            return result;
            
            
        }

        public bool Delete(int id)
        {
            if (conn.State == ConnectionState.Closed) conn.Open();
            string sqlcom = "DELETE FROM Products WHERE _id= @id";
            SqlCommand cm = new SqlCommand(sqlcom, conn);
            cm.Parameters.AddWithValue("@id", id);
            int n = cm.ExecuteNonQuery();
            bool result = (n > 0);
            return result;
        }
         public void UpdateAfterDelete(int id)
         {
             if (conn.State == ConnectionState.Closed) conn.Open();
             string sqlcom = "UPDATE Products SET _id=_id - 1 WHERE _id > @id ";
             SqlCommand cm = new SqlCommand(sqlcom, conn);
             cm.Parameters.AddWithValue("@id" , id);
             cm.ExecuteNonQuery();
         }

         public DataTable getDataLogin()
         {
             string sql = "Select * from staff";
             SqlDataAdapter adapter = new SqlDataAdapter(sql, conn);
             DataTable dtUser = new DataTable();
             adapter.Fill(dtUser);
             return dtUser;
         }
    }
}
